﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MJM.SCA.EntidadesNegocio;
using MJM.CBD.Conn;

namespace MJM.SCA.AccesoDatos
{
    public class LogTramiteAD
    {
        public List<LogTramiteEN> Listar(string FechaInicio,string FechaFin,string TipoDocumento)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGAW_CLIENT_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmFecIni, prmFecFin, prmTipodoc;
            List<LogTramiteEN> lst = new List<LogTramiteEN>();
            SqlDataReader dr;

            try
            {
                prmFecFin = cmd.CreateParameter();
                prmFecFin.ParameterName = "@vFechaFin";
                prmFecFin.SqlDbType = SqlDbType.VarChar;
                prmFecFin.Value = FechaFin;
                cmd.Parameters.Add(prmFecFin);

                prmFecIni = cmd.CreateParameter();
                prmFecIni.ParameterName = "@vFechaIni";
                prmFecIni.SqlDbType = SqlDbType.VarChar;
                prmFecIni.Value = FechaInicio;
                cmd.Parameters.Add(prmFecIni);

                prmTipodoc = cmd.CreateParameter();
                prmTipodoc.ParameterName = "@vLOGAW_chNOMAPL";
                prmTipodoc.SqlDbType = SqlDbType.VarChar;
                prmTipodoc.Value = TipoDocumento;
                cmd.Parameters.Add(prmTipodoc);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                LogTramiteEN obj;

                while (dr.Read())
                {
                    obj = new LogTramiteEN();
                    obj.LOGAW_P_inID = dr.GetInt32(0);
                    obj.LOGAW_chIPCLIEN = dr.GetString(1);
                    obj.LOGAW_chFECREG = dr.GetString(2);
                    obj.LOGAW_chHORREG = dr.GetString(3);
                    obj.LOGAW_chNOMAPL = dr.GetString(4);
                    obj.LOGAW_chPARUNO = dr.GetString(5);
                    obj.LOGAW_chPARDOS = dr.GetString(6);
                    obj.LOGAW_chPARTRE = dr.GetString(7);
                    obj.LOGAW_chFECMOD = dr.GetString(8);
                    obj.LOGAW_inHORMOD = dr.GetInt32(9);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }
        //
        public int Insertar(LogTramiteEN obj)
        {
            int codigo = 0;
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_i_T_LOGAW_CLIENT_Insertar_RC1", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmIpCliente,prmFecha,prmHora,prmNomAplicacion,prmParUno,prmParDos,prmParTres,prmFecMod,prmHoraMod,prmUserMod;

            try
            {
                prmIpCliente = cmd.CreateParameter();
                prmIpCliente.ParameterName = "@vLOGAW_chIPCLIEN";
                prmIpCliente.SqlDbType = SqlDbType.VarChar;
                prmIpCliente.Value = obj.LOGAW_chIPCLIEN;
                cmd.Parameters.Add(prmIpCliente);

                prmFecha = cmd.CreateParameter();
                prmFecha.ParameterName = "@vLOGAW_chFECREG";
                prmFecha.SqlDbType = SqlDbType.VarChar;
                prmFecha.Value = obj.LOGAW_chFECREG;
                cmd.Parameters.Add(prmFecha);

                prmHora = cmd.CreateParameter();
                prmHora.ParameterName = "@vLOGAW_chHORREG";
                prmHora.SqlDbType = SqlDbType.VarChar;
                prmHora.Value = obj.LOGAW_chHORREG;
                cmd.Parameters.Add(prmHora);

                prmNomAplicacion = cmd.CreateParameter();
                prmNomAplicacion.ParameterName = "@vLOGAW_chNOMAPL";
                prmNomAplicacion.SqlDbType = SqlDbType.VarChar;
                prmNomAplicacion.Value = obj.LOGAW_chNOMAPL;
                cmd.Parameters.Add(prmNomAplicacion);

                prmParUno = cmd.CreateParameter();
                prmParUno.ParameterName = "@vLOGAW_chPARUNO";
                prmParUno.SqlDbType = SqlDbType.VarChar;
                prmParUno.Value = obj.LOGAW_chPARUNO;
                cmd.Parameters.Add(prmParUno);

                prmParDos = cmd.CreateParameter();
                prmParDos.ParameterName = "@vLOGAW_chPARDOS";
                prmParDos.SqlDbType = SqlDbType.VarChar;
                prmParDos.Value = obj.LOGAW_chPARDOS;
                cmd.Parameters.Add(prmParDos);

                prmParTres = cmd.CreateParameter();
                prmParTres.ParameterName = "@vLOGAW_chPARTRE";
                prmParTres.SqlDbType = SqlDbType.VarChar;
                prmParTres.Value = obj.LOGAW_chPARTRE;
                cmd.Parameters.Add(prmParTres);

                prmHoraMod = cmd.CreateParameter();
                prmHoraMod.ParameterName = "@vLOGAW_inHORMOD";
                prmHoraMod.SqlDbType = SqlDbType.VarChar;
                prmHoraMod.Value = obj.LOGAW_inHORMOD;
                cmd.Parameters.Add(prmHoraMod);

                prmFecMod = cmd.CreateParameter();
                prmFecMod.ParameterName = "@vLOGAW_chFECMOD";
                prmFecMod.SqlDbType = SqlDbType.VarChar;
                prmFecMod.Value = obj.LOGAW_chFECMOD;
                cmd.Parameters.Add(prmFecMod);

                prmUserMod = cmd.CreateParameter();
                prmUserMod.ParameterName = "@vLOGAW_chUSRDOM";
                prmUserMod.SqlDbType = SqlDbType.VarChar;
                prmUserMod.Value = "";
                cmd.Parameters.Add(prmUserMod);

                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                codigo = 1;

                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                codigo = 0;
                cmd.Dispose();
                conn.Dispose();
            }

            return codigo;
        }

        public List<LogTramiteEN> ListarLogins(string FechaInicio, string FechaFin, string Aplicacion)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_AUDLOG_APPMUN_Lista", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmFecIni, prmFecFin, prmTipodoc;
            List<LogTramiteEN> lst = new List<LogTramiteEN>();
            SqlDataReader dr;

            try
            {
                prmFecFin = cmd.CreateParameter();
                prmFecFin.ParameterName = "@vFECFIN";
                prmFecFin.SqlDbType = SqlDbType.VarChar;
                prmFecFin.Value = FechaFin;
                cmd.Parameters.Add(prmFecFin);

                prmFecIni = cmd.CreateParameter();
                prmFecIni.ParameterName = "@vFECINI";
                prmFecIni.SqlDbType = SqlDbType.VarChar;
                prmFecIni.Value = FechaInicio;
                cmd.Parameters.Add(prmFecIni);

                prmTipodoc = cmd.CreateParameter();
                prmTipodoc.ParameterName = "@vAPP";
                prmTipodoc.SqlDbType = SqlDbType.VarChar;
                prmTipodoc.Value = Aplicacion;
                cmd.Parameters.Add(prmTipodoc);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                LogTramiteEN obj;

                while (dr.Read())
                {
                    obj = new LogTramiteEN();
                    obj.LOGAW_P_inID = dr.GetInt64(dr.GetOrdinal("AUDLOG_P_inID"));
                    obj.LOGAW_chIPCLIEN = dr.GetString(dr.GetOrdinal("AUDLOG_chDIRIP"));
                    obj.LOGAW_chFECREG = dr.GetString(dr.GetOrdinal("AUDLOG_chFECCOM"));
                    obj.LOGAW_chNOMAPL = dr.GetString(dr.GetOrdinal("AUDLOG_chNOMAPP"));
                    obj.LOGAW_chPARUNO = dr.GetString(dr.GetOrdinal("AUDLOG_chEXCLOG"));
                    obj.LOGAW_chPARDOS = dr.GetString(dr.GetOrdinal("AUDLOG_chUSULOG"));
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<LogTramiteEN> ListarAccesos(string FechaInicio, string FechaFin, string Aplicacion)
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_AUDACT_APPMUN_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmFecIni, prmFecFin, prmTipodoc;
            List<LogTramiteEN> lst = new List<LogTramiteEN>();
            SqlDataReader dr;

            try
            {
                prmFecFin = cmd.CreateParameter();
                prmFecFin.ParameterName = "@vFECFIN";
                prmFecFin.SqlDbType = SqlDbType.VarChar;
                prmFecFin.Value = FechaFin;
                cmd.Parameters.Add(prmFecFin);

                prmFecIni = cmd.CreateParameter();
                prmFecIni.ParameterName = "@vFECINI";
                prmFecIni.SqlDbType = SqlDbType.VarChar;
                prmFecIni.Value = FechaInicio;
                cmd.Parameters.Add(prmFecIni);

                prmTipodoc = cmd.CreateParameter();
                prmTipodoc.ParameterName = "@vAPP";
                prmTipodoc.SqlDbType = SqlDbType.VarChar;
                prmTipodoc.Value = Aplicacion;
                cmd.Parameters.Add(prmTipodoc);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                LogTramiteEN obj;

                while (dr.Read())
                {
                    obj = new LogTramiteEN();
                    obj.LOGAW_P_inID = dr.GetInt64(dr.GetOrdinal("AUDACT_P_inID"));
                    obj.LOGAW_chIPCLIEN = dr.GetString(dr.GetOrdinal("AUDACT_chDIRIP"));
                    obj.LOGAW_chFECREG = dr.GetString(dr.GetOrdinal("AUDACT_chFECCOM"));
                    obj.LOGAW_chNOMAPL = dr.GetString(dr.GetOrdinal("AUDACT_chNOMAPP"));
                    obj.LOGAW_chPARUNO = dr.GetString(dr.GetOrdinal("AUDACT_chNOMACC")) + " (" + dr.GetString(dr.GetOrdinal("AUDACT_chVALENV"))+")";
                    obj.LOGAW_chPARTRE= dr.GetString(dr.GetOrdinal("AUDACT_chNOMMOD"));
                    obj.LOGAW_chPARDOS = dr.GetString(dr.GetOrdinal("AUDACT_chUSUACT"));
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<String> ListarAplicaciones(int tipo)
        { 
            //1 login
            //2 acciones
            List<String> lst = new List<string>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            string comando="";
            if (tipo == 1)
                comando = "sp_s_T_AUDLOG_APPMUN_Aplicaciones";
            else
                comando = "sp_s_T_AUDACT_APPMUN_Aplicaciones";

            SqlCommand cmd = new SqlCommand(comando, conn);

            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    lst.Add(dr.GetString(0));
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }
    }
}
